package com.yulian.repository.supplier_management;

import java.util.Iterator;

import com.healthmarketscience.sqlbuilder.CustomSql;
import com.healthmarketscience.sqlbuilder.InCondition;
import com.healthmarketscience.sqlbuilder.SelectQuery;

import io.netty.util.internal.logging.InternalLogger;
import io.netty.util.internal.logging.Log4JLoggerFactory;
import io.vertx.core.Future;
import io.vertx.core.Promise;
import io.vertx.core.json.JsonObject;
import io.vertx.sqlclient.Row;
import io.vertx.sqlclient.RowSet;
import io.vertx.sqlclient.SqlConnection;
import io.vertx.sqlclient.Tuple;

public class GoodsPriceModeRepositoryImpl {
	final InternalLogger logger = Log4JLoggerFactory.getInstance(GoodsPriceModeRepositoryImpl.class);

    /**
     * 此方法主要通过传入的查询参数，构建查询数据表的sql语句，返回满足条件的记录数量；其中参数如下
     * @param conn  数据库连接对象
     * @param queryJSObj 含查询参数的Json对象
     * @return
     */
    public Future<RowSet<Row>> getRecordsTotal(SqlConnection conn, JsonObject queryJSObj){
        Promise<RowSet<Row>> promise = Promise.promise();
        String sql = this.creaStrGetRecordsTotalWithJson(queryJSObj) ;
        conn
        .preparedQuery(sql)
        .execute( ar2 -> {
            //Release the connection to the pool
            conn.close();
            if(ar2.succeeded()){
                logger.info("getRecordsTotal, 查询所得数量: {}", ar2.result().rowCount());
                promise.complete(ar2.result());
            }else{
                logger.info("getRecordsTotal, 查询失败, 失败原因: {}", ar2.cause().getMessage() );
                //用Promise来向上抛异常
                promise.fail(ar2.cause());
            }
        });
        return promise.future();
    }

    /**
     * 此方法创建sql语句,在goods_price_mode表中查询符合搜索条件的记录总数
     * @param queryJS
     * @return
     */
    public String creaStrGetRecordsTotalWithJson(JsonObject queryJS) {

        // 定义 SelectQuery 对象
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("count(*) as total ") )
                .addCustomFromTable("goods_price_mode");
        //通过循环取出Json对象中的查询参数，并添加到 InCondition中
        Iterator<String> iter =  queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key =  iter.next();
            try {
                String value = queryJS.getString(key);
                if(!value.equals("") && !value.equals("[]") ) {
                    String firstChar = value.substring(0,1);
                    if(firstChar.equals("["))
                        value = value.substring(1,value.length()-1);
                    value = value.replace(", ", "','");
                    //添加含InCondtion的条件的where子句
                    sq.getWhereClause().addCustomCondition(new InCondition( new CustomSql(key),  value));
                }

            } catch (Exception e) {
                logger.info("creaStrGetRecordsTotalWithJson, 发生错误,错误原因: ",e.getMessage());
            }
        }
        return sq.validate().toString();
    }

    /**
     * 在goods_price_mode表中查询符合搜索条件的所有记录
     * @param conn
     * @param queryJSObj
     * @param query_offset
     * @param query_limit
     * @return
     */

    public Future<RowSet<Row>> getRecords(SqlConnection conn, JsonObject queryJSObj, int query_offset, int query_limit){
        Promise<RowSet<Row>> promise = Promise.promise();
        String sql = this.creaStrGetRecordsWithJson(queryJSObj,query_offset, query_limit) ;
        conn
        .preparedQuery(sql)
        .execute( ar2 -> {
            //Release the connection to the pool
            conn.close();
            if(ar2.succeeded()){
                logger.info("getRecords, 记录数: ",  ar2.result().rowCount());
                promise.complete(ar2.result());
            }else{
                logger.info("getRecords, 失败,失败原因:{}", ar2.cause().getMessage());
                //用Promise来向上抛异常
                promise.fail(ar2.cause());
            }
        });
        return promise.future();
    }
    /**
     * 此方法创建sql语句,在goods_price_mode表中查询符合搜索条件的所有记录
     * @param queryJS
     * @param query_offset
     * @param query_limit
     * @return
     */
    public String creaStrGetRecordsWithJson(JsonObject queryJS, int query_offset,int query_limit) {
        // 定义 SelectQuery 对象
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("id"), new CustomSql("price_mode_code"), new CustomSql("price_mode_name"),new CustomSql("is_delete"),
                        new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time") )
                .addCustomFromTable("goods_price_mode");

        //通过循环取出Json对象中的查询参数，并添加到 InCondition中
        Iterator<String> iter =   queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if( !value.equals("") && !value.equals("[]") ) {
                    String firstChar = value.substring(0,1);
                    if(firstChar.equals("["))
                        value = value.substring(1,value.length()-1);
                    value = value.replace(", ", "','");
                    //添加含InCondtion的条件的where子句
                    sq.getWhereClause().addCustomCondition(new InCondition( new CustomSql(key),  value));
                }

            } catch (Exception e) {
                logger.info("creaStrGetRecordsWithJson, 发生错误,错误原因: ",e.getMessage());
            }
        }

        String sql  = sq.validate().toString();
        return  sql;
    }



    /**
     * 此方法判断传入计价模式名称是否存在，为添加数据使用的
     * @param conn
     * @param price_mode_name
     * @return 该名称的记录数
     */
    public Future<RowSet<Row>> getPriceModeName(SqlConnection conn, String price_mode_name){
        Promise<RowSet<Row>> promise = Promise.promise();
        String sql = "select price_mode_name from goods_price_mode where price_mode_name = $1  order by id desc limit 1";
        conn
        .preparedQuery(sql)
        .execute(Tuple.of( price_mode_name) , ar -> {
            conn.close();
            if(ar.succeeded()){
                logger.info("getPriceModeName, 记录数: ",  ar.result().rowCount());
                promise.complete(ar.result());
            }else{
                logger.info("getPriceModeName, 失败,失败原因:{}", ar.cause().getMessage());
                //这里就相当于继续向上抛出异常，用Promise来向上抛异常
                promise.fail(ar.cause());
            }
        });
        return promise.future();
    }

    /**
     * 根据传入当前记录的id以及编辑后的计价模式名字price_mode_name，判断表中是否存在
     * @param conn
     * @param id
     * @param price_mode_name
     * @return
     */

    public Future<RowSet<Row>> getPriceModeNameById(SqlConnection conn, long id, String price_mode_name){
        Promise<RowSet<Row>> promise = Promise.promise();
        String sql = "select price_mode_name from goods_price_mode where price_mode_name = $1 and id != $2  order by id desc limit 1";
        conn
        .preparedQuery(sql)
        .execute(Tuple.of(price_mode_name,id) , ar -> {
            conn.close();
            if(ar.succeeded()){
                logger.info("getPriceModeNameById, 记录数: ",  ar.result().rowCount());
                promise.complete(ar.result());
            }else{
                logger.info("getPriceModeNameById, 失败,失败原因:{}", ar.cause().getMessage());
                //这里就相当于继续向上抛出异常，用Promise来向上抛异常
                promise.fail(ar.cause());
            }
        });
        return promise.future();
    }

    /**
     * 此方法在goods_price_mode 中获取最新记录的等级编码
     */
    public Future<RowSet<Row>> getPriceModeCode(SqlConnection conn ){
        Promise<RowSet<Row>> promise = Promise.promise();
        String sql = "select price_mode_code from goods_price_mode order by id desc limit 1";
        conn
        .preparedQuery(sql)
        .execute(  ar -> {
            conn.close();
            if(ar.succeeded()){
                logger.info("getPriceModeCode, 记录数: ",  ar.result().rowCount());
                promise.complete(ar.result());
            }else{
                logger.info("getPriceModeCode, 失败,失败原因:{}", ar.cause().getMessage());
                //这里就相当于继续向上抛出异常，用Promise来向上抛异常
                promise.fail(ar.cause());
            }
        });
        return promise.future();
    }

    /***
     * 此方法实现在goods_price_mode中新增一条记录
     * @param conn 连接
     * @param price_mode_code 计价模式编码
     * @param price_mode_name 计价模式名称
     * @param is_delete 有效状态
     * @param create_user 创建者
     * @return
     */
    public Future<RowSet<Row>> insertGoodsPriceMode(SqlConnection conn, String price_mode_code, String price_mode_name,   String create_user){
        Promise<RowSet<Row>> promise = Promise.promise();
        String sql = "INSERT INTO goods_price_mode(price_mode_code, price_mode_name,  create_user)"
                + " VALUES ($1, $2, $3)  RETURNING id" ;
        conn.preparedQuery(sql)
        .execute(Tuple.of(price_mode_code, price_mode_name, create_user), ar2 -> {
            //释放一个连接到连接池
            conn.close();
            if(ar2.succeeded()){
                logger.info("insertGoodsPriceMode, 插入记录数:{} ", ar2.result().rowCount());
                promise.complete(ar2.result());
            }else{
                logger.info("insertGoodsPriceMode, 插入失败,失败原因:{}",ar2.cause().getMessage());
                //用Promise来向上抛异常
                promise.fail(ar2.cause());
            }
        });
        return promise.future();
    }

    /**
     * 此方法实现在goods_price_mode中根据id更新一条记录
     * @param conn 连接
     * @param price_mode_code 计价模式编码
     * @param price_mode_name 计价模式名称
     * @param is_delete 有效状态
     * @param update_user 更新者
     * @return
     */
    public Future<RowSet<Row>> updateGoodsPriceModeById(SqlConnection conn, long id,  String price_mode_name,   String update_user){
        Promise<RowSet<Row>> promise = Promise.promise();
        String sql = "update goods_price_mode set   price_mode_name=$1, update_user=$2 where id=$3 RETURNING id" ;
        conn
        .preparedQuery(sql)
        .execute(Tuple.of( price_mode_name,   update_user,id) , ar -> {
            //Release the connection to the pool
            conn.close();
            if(ar.succeeded()){
                logger.info("updateGoodsPriceModeById, 更新记录数: {}", ar.result().rowCount());
                promise.complete(ar.result());
            } else{
                logger.info("updateGoodsPriceModeById, 更新失败,失败原因:{}",ar.cause().getMessage());
                //这里就相当于继续向上抛出异常，用Promise来向上抛异常
                promise.fail(ar.cause());
            }
        });
        return promise.future();
    }

    public Future<RowSet<Row>> deleteGoodsPriceModeById(SqlConnection conn,long id ){
        Promise<RowSet<Row>> promise = Promise.promise();
        String sql= "delete from  goods_price_mode   where id = $1 RETURNING id" ;
        conn
                .preparedQuery(sql)
                .execute(Tuple.of(id) , ar -> {
                    conn.close();
                    if(ar.succeeded()){
                        logger.info("deleteGoodsPriceModeById, 物理删除记录数:{} ", ar.result().rowCount());
                        promise.complete(ar.result());
                    }else{
                        logger.info("deleteGoodsPriceModeById, 物理删除失败,失败原因:{}",ar.cause().getMessage());
                        //用Promise来向上抛异常
                        promise.fail(ar.cause());
                    }
                });
        return promise.future();
    }

    /**
     * 此方法实现在goods_price_mode中根据id_list去逻辑删除多条记录
     * @param conn
     * @param is_delete
     * @param id_list
     * @param update_user
     * @return
     */
    public Future<RowSet<Row>> deleteGoodsPriceMode(SqlConnection conn, int is_delete,String id_list, String update_user){
        Promise<RowSet<Row>> promise = Promise.promise();
        String sql= "update goods_price_mode set is_delete = $1, update_user = $2 where id = any(array" + id_list + ") RETURNING id" ;
        conn
                .preparedQuery(sql)
                .execute(Tuple.of(is_delete,update_user) , ar -> {
                    conn.close();
                    if(ar.succeeded()){
                        logger.info("deleteGoodsPriceMode, 逻辑删除记录数:{} ", ar.result().rowCount());
                        promise.complete(ar.result());
                    }else{
                        logger.info("deleteGoodsPriceMode, 逻辑删除失败,失败原因:{}",ar.cause().getMessage());
                        //用Promise来向上抛异常
                        promise.fail(ar.cause());
                    }
                });
        return promise.future();
    }

}
